package ro.msg.quiz.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ro.msg.quiz.domain.MultipleChoiceQuestion;
import ro.msg.quiz.domain.Question;
import ro.msg.quiz.domain.QuestionType;
import ro.msg.quiz.domain.SingleChoiceQuestion;
import ro.msg.quiz.domain.TextQuestion;

//AUTHORS: Ervin, Marco, Roxana

public class QuestionDB {

    public static void createQuestion(String text, String type) throws ClassNotFoundException, SQLException {
	String sql = "INSERT into questions (text , type) values ('" + text + "' , '" + type + "')";
	DatabaseCon.getInstance().executeUpdate(sql);
    }

    public static void updateQuestion(int idQuestion, String text, String type) throws SQLException, ClassNotFoundException{
	String sql = "UPDATE questions SET text = '" + text + "', type = '" + type + "' where id = " + idQuestion;
	DatabaseCon.getInstance().executeUpdate(sql);
    }
    
    public static void deleteQuestion(String text) throws ClassNotFoundException, SQLException {
	String sql1 = "DELETE from questions where text = '" + text + "'";
	DatabaseCon.getInstance().executeUpdate(sql1);
    }

    public static ResultSet getQuestions() throws ClassNotFoundException, SQLException {
	String sql = "SELECT * from questions";
	return DatabaseCon.getInstance().executeQuery(sql);
    }

    public static int getQuestionId(String text) throws ClassNotFoundException, SQLException {
	String sql = "SELECT id from questions where text = '" + text + "'";
	ResultSet rs = DatabaseCon.getInstance().executeQuery(sql);
	rs.next();
	return rs.getInt(1);
    }

    public static List<Question> populateQuestions() throws Exception {
	Connection con = null;
	con = DatabaseCon.getInstance().getConnection();
	List<Question> questionList = new ArrayList<Question>();

	Statement s1 = con.createStatement();
	s1.setMaxRows(100);
	ResultSet rs = s1.executeQuery("select * from " + "questions");
	while (rs.next()) {
	    int id = rs.getInt(1);
	    String text = rs.getString(2);
	    Statement s2 = con.createStatement();
	    ResultSet rsAnsw = s2.executeQuery("select * from answers where idQuestion =" + Integer.toString(id));
	    List<String> options = new ArrayList<String>();
	    if (rs.getObject(3).equals(QuestionType.SINGLE_CHOICE_Q.toString())) {
		int correctIndex = 0;
		int index = -1;
		while (rsAnsw.next()) {
		    index++;
		    options.add(rsAnsw.getString(3));
		    if (rsAnsw.getBoolean(4) == true) {
			correctIndex = index;
		    }
		}

		Question q = new SingleChoiceQuestion(id, text, options, correctIndex);
		questionList.add(q);
	    } else if (rs.getObject(3).equals(QuestionType.MULTIPLE_CHOICE_Q.toString())) {
		int index = -1;
		ArrayList<Integer> correctIndex = new ArrayList<Integer>();
		while (rsAnsw.next()) {
		    index++;
		    options.add(rsAnsw.getString(3));
		    if (rsAnsw.getBoolean(4) == true) {
			correctIndex.add(index);
		    }
		}
		Question q = new MultipleChoiceQuestion(id, text, options, correctIndex);
		questionList.add(q);
	    } else {
		rsAnsw.next();
		String answ = rsAnsw.getString(3);
		Question q = new TextQuestion(id, text, answ);
		questionList.add(q);
	    }
	}
	return questionList;
    }
}
